package com.ld.shieldsb.canalclient.util;

import java.io.Reader;
import java.io.StringReader;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.ld.shieldsb.canalclient.handler.config.MappingConfig;
import com.ld.shieldsb.common.core.util.date.DateUtil;

public class SyncUtil {

    /**
     * 返回目标列的映射map，目标列为key，源列为value
     * 
     * @Title getColumnsMap
     * @author 吕凯
     * @date 2021年12月6日 上午10:03:46
     * @param dbMapping
     * @param data
     * @return Map<String,String>
     */
    public static Map<String, String> getColumnsMap(MappingConfig.DbMapping dbMapping, Map<String, Object> data) {
        return getColumnsMap(dbMapping, data.keySet());
    }

    /**
     * 获取列映射，目标列为key，源列为value
     * 
     * @Title getColumnsMap
     * @author 吕凯
     * @date 2022年1月14日 下午2:57:13
     * @param dbMapping
     * @param columns
     *            源列名
     * @return Map<String,String>
     */
    public static Map<String, String> getColumnsMap(MappingConfig.DbMapping dbMapping, Collection<String> columns) {
        Map<String, String> columnsMap;
        if (dbMapping.isMapAll()) { // 如果设置了所有字段匹配
            if (dbMapping.getAllMapColumns() != null) { // 如果设置了字段map则直接返回，否则循环数据中的map，这个地方貌似有问题，更改了引用值
                return dbMapping.getAllMapColumns();
            }
            // 无缓存则取设置的值
            columnsMap = new LinkedHashMap<>();
            for (String srcColumn : columns) {
                columnsMap.put(srcColumn, srcColumn);
            }
            dbMapping.setAllMapColumns(columnsMap);
        } else { // 未设置，则取targetColumns
            columnsMap = dbMapping.getTargetColumns();
        }
        return columnsMap;
    }

    /**
     * 根据类型将值设置到 preparedStatement 中
     *
     * @param type
     *            sqlType数据类型
     * @param pstmt
     *            需要设置的preparedStatement
     * @param value
     *            数据值
     * @param index
     *            索引号
     */
    public static void setPStmt(int type, PreparedStatement pstmt, Object value, int index) throws SQLException {
        switch (type) {
            case Types.BIT:
            case Types.BOOLEAN:
                if (value instanceof Boolean) {
                    pstmt.setBoolean(index, (Boolean) value);
                } else if (value instanceof String) {
                    boolean v = !value.equals("0");
                    pstmt.setBoolean(index, v);
                } else if (value instanceof Number) {
                    boolean v = ((Number) value).intValue() != 0;
                    pstmt.setBoolean(index, v);
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.CHAR:
            case Types.NCHAR:
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                if (value instanceof String) {
                    pstmt.setString(index, (String) value);
                } else if (value == null) {
                    pstmt.setNull(index, type);
                } else {
                    pstmt.setString(index, value.toString());
                }
                break;
            case Types.TINYINT:
                // 向上提升一级，处理unsigned情况
                if (value instanceof Number) {
                    pstmt.setShort(index, ((Number) value).shortValue());
                } else if (value instanceof String) {
                    pstmt.setShort(index, Short.parseShort((String) value));
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.SMALLINT:
                if (value instanceof Number) {
                    pstmt.setInt(index, ((Number) value).intValue());
                } else if (value instanceof String) {
                    pstmt.setInt(index, Integer.parseInt((String) value));
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.INTEGER:
                if (value instanceof Number) {
                    pstmt.setLong(index, ((Number) value).longValue());
                } else if (value instanceof String) {
                    pstmt.setLong(index, Long.parseLong((String) value));
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.BIGINT:
                if (value instanceof Number) {
                    pstmt.setBigDecimal(index, new BigDecimal(value.toString()));
                } else if (value instanceof String) {
                    pstmt.setBigDecimal(index, new BigDecimal(value.toString()));
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.DECIMAL:
            case Types.NUMERIC:
                if (value instanceof BigDecimal) {
                    pstmt.setBigDecimal(index, (BigDecimal) value);
                } else if (value instanceof Byte) {
                    pstmt.setInt(index, ((Byte) value).intValue());
                } else if (value instanceof Short) {
                    pstmt.setInt(index, ((Short) value).intValue());
                } else if (value instanceof Integer) {
                    pstmt.setInt(index, (Integer) value);
                } else if (value instanceof Long) {
                    pstmt.setLong(index, (Long) value);
                } else if (value instanceof Float) {
                    pstmt.setBigDecimal(index, new BigDecimal((float) value));
                } else if (value instanceof Double) {
                    pstmt.setBigDecimal(index, new BigDecimal((double) value));
                } else if (value != null) {
                    pstmt.setBigDecimal(index, new BigDecimal(value.toString()));
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.REAL:
                if (value instanceof Number) {
                    pstmt.setFloat(index, ((Number) value).floatValue());
                } else if (value instanceof String) {
                    pstmt.setFloat(index, Float.parseFloat((String) value));
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.FLOAT:
            case Types.DOUBLE:
                if (value instanceof Number) {
                    pstmt.setDouble(index, ((Number) value).doubleValue());
                } else if (value instanceof String) {
                    pstmt.setDouble(index, Double.parseDouble((String) value));
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
            case Types.BLOB:
                if (value instanceof Blob) {
                    pstmt.setBlob(index, (Blob) value);
                } else if (value instanceof byte[]) {
                    pstmt.setBytes(index, (byte[]) value);
                } else if (value instanceof String) {
                    pstmt.setBytes(index, ((String) value).getBytes(StandardCharsets.ISO_8859_1));
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.CLOB:
                if (value instanceof Clob) {
                    pstmt.setClob(index, (Clob) value);
                } else if (value instanceof byte[]) {
                    pstmt.setBytes(index, (byte[]) value);
                } else if (value instanceof String) {
                    Reader clobReader = new StringReader((String) value);
                    pstmt.setCharacterStream(index, clobReader);
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.DATE:
                if (value instanceof java.sql.Date) {
                    pstmt.setDate(index, (java.sql.Date) value);
                } else if (value instanceof java.util.Date) {
                    pstmt.setDate(index, new java.sql.Date(((java.util.Date) value).getTime()));
                } else if (value instanceof String) {
                    String v = (String) value;
                    if (!v.startsWith("0000-00-00")) {
                        java.util.Date date = DateUtil.string2Date(v);
                        if (date != null) {
                            pstmt.setDate(index, new Date(date.getTime()));
                        } else {
                            pstmt.setNull(index, type);
                        }
                    } else {
                        pstmt.setObject(index, value);
                    }
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.TIME:
                if (value instanceof java.sql.Time) {
                    pstmt.setTime(index, (java.sql.Time) value);
                } else if (value instanceof java.util.Date) {
                    pstmt.setTime(index, new java.sql.Time(((java.util.Date) value).getTime()));
                } else if (value instanceof String) {
                    String v = (String) value;
                    java.util.Date date = DateUtil.string2Date(v);
                    if (date != null) {
                        pstmt.setTime(index, new Time(date.getTime()));
                    } else {
                        pstmt.setNull(index, type);
                    }
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            case Types.TIMESTAMP:
                if (value instanceof java.sql.Timestamp) {
                    pstmt.setTimestamp(index, (java.sql.Timestamp) value);
                } else if (value instanceof java.util.Date) {
                    pstmt.setTimestamp(index, new java.sql.Timestamp(((java.util.Date) value).getTime()));
                } else if (value instanceof String) {
                    String v = (String) value;
                    if (!v.startsWith("0000-00-00")) {
                        java.util.Date date = DateUtil.string2Date(v);
                        if (date != null) {
                            pstmt.setTimestamp(index, new Timestamp(date.getTime()));
                        } else {
                            pstmt.setNull(index, type);
                        }
                    } else {
                        pstmt.setObject(index, value);
                    }
                } else {
                    pstmt.setNull(index, type);
                }
                break;
            default:
                pstmt.setObject(index, value, type);
        }
    }

    /**
     * 获取表名
     * 
     * @Title getDbTableName
     * @author 吕凯
     * @date 2021年12月7日 下午5:31:01
     * @param dbMapping
     * @return String
     */
    public static String getDbTableName(MappingConfig.DbMapping dbMapping) {
        String result = "";
        if (StringUtils.isNotEmpty(dbMapping.getTargetDb())) {
            result += ("`" + dbMapping.getTargetDb() + "`.");
        }
        result += ("`" + dbMapping.getTargetTable() + "`");
        return result;
    }

    /**
     * 获取忽略大小写后相等的实际列名
     * 
     * @Title getIgnoreCaseKey
     * @author 吕凯
     * @date 2021年12月22日 上午11:34:58
     * @param ctype
     *            字段和类型的映射
     * @param targetColumnName
     * @return String
     */
    public static String getIgnoreCaseColunm(Map<String, ?> ctype, String targetColumnName) {
        return ctype.keySet().stream().filter(key -> key.equalsIgnoreCase(CanalUtil.cleanColumn(targetColumnName))).findFirst()
                .orElse(null);
    }

}
